﻿import * as XLSX from 'xlsx';
import * as moment from 'moment';

/**
 * 这是 自定义的 XLSX 帮助方法
 */
export class XlsxJsHelper {
    /**
     * 把 sheet 数据 转换为 cell 数组
     * @param sheet sheet data
     */
    static sheet_to_array(sheet) {
        console.log(sheet);
        let y = '',
            x,
            val = '';
        if (sheet == null || sheet['!ref'] == null) {
            return [];
        }
        let r = this.safe_decode_range(sheet['!ref']),
            rr = '',
            cols = [],
            C;
        let cmds = [];
        let dense = Array.isArray(sheet);
        for (C = r.s.c; C <= r.e.c; ++C) {
            cols[C] = XLSX.utils.encode_col(C);
        }
        for (let R = r.s.r; R <= r.e.r; ++R) {
            rr = XLSX.utils.encode_row(R);
            for (C = r.s.c; C <= r.e.c; ++C) {
                y = cols[C] + rr;
                x = dense ? (sheet[R] || [])[C] : sheet[y];
                val = '';

                if (x === undefined) {
                    continue;
                } else if (x.F != null) {
                    y = x.F;
                    if (!x.f) {
                        continue;
                    }
                    val = x.f;
                    if (y.indexOf(':') === -1) {
                        y = y + ':' + y;
                    }
                }
                if (x.w === undefined) {
                    continue;
                } else {
                    if (x.t === 'd') {
                        val = moment(x.v).format('YYYY-MM-DD');
                    } else {
                        val = '' + x.w;
                    }
                }

                cmds[cmds.length] = y + '=' + val;
            }
        }
        return cmds;
    }

    /**
     * 获得有效的 数据范围
     * @param range sheet range
     */
    static safe_decode_range(range) {
        let o = { s: { c: 0, r: 0 }, e: { c: 0, r: 0 } };
        let idx = 0,
            i = 0,
            cc = 0;
        let len = range.length;
        for (idx = 0; i < len; ++i) {
            if ((cc = range.charCodeAt(i) - 64) < 1 || cc > 26) {
                break;
            }
            idx = 26 * idx + cc;
        }
        o.s.c = --idx;

        for (idx = 0; i < len; ++i) {
            if ((cc = range.charCodeAt(i) - 48) < 0 || cc > 9) {
                break;
            }
            idx = 10 * idx + cc;
        }
        o.s.r = --idx;

        if (i === len || range.charCodeAt(++i) === 58) {
            o.e.c = o.s.c;
            o.e.r = o.s.r;
            return o;
        }

        for (idx = 0; i !== len; ++i) {
            if ((cc = range.charCodeAt(i) - 64) < 1 || cc > 26) {
                break;
            }
            idx = 26 * idx + cc;
        }
        o.e.c = --idx;

        for (idx = 0; i !== len; ++i) {
            if ((cc = range.charCodeAt(i) - 48) < 0 || cc > 9) {
                break;
            }
            idx = 10 * idx + cc;
        }
        o.e.r = --idx;
        return o;
    }
}
